﻿Imports System.Data.SqlClient
Imports System.IO

Public Class frmStaffAbsence

    Sub FillStaff()
        Try
            Me.Cursor = Cursors.Default

            Dim cmd As New SqlCommand("select EmpNo,FullNameEn from StaffProfiles", cnn)
            Dim Reader As SqlDataReader

            Me.ListUsers.Items.Clear()
            Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Dim Item As New ListViewItem
                Item.Text = Reader.Item("EmpNo")
                Item.SubItems.Add(Reader.Item("FullNameEn"))
                Me.ListUsers.Items.Add(Item)
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillAbsenceHistory()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From Absence Where EmpNo=" & Me.txtEmpNo.Text.Trim, cnn)
            Dim Reader As SqlDataReader

            Me.GridAbsenceHistory.Rows.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.GridAbsenceHistory.Rows.Add({Reader.Item("SNo"), CDate(Reader.Item("AbsentDateFrom")).ToString("dd-MMM-yyyy"), _
                                           CDate(Reader.Item("AbsentDateTo")).ToString("dd-MMM-yyyy"), Reader.Item("AbsenceNoOfDays"), _
                                           CheckYesNo(Reader.Item("DeductedfromVacation")), Reader.Item("LeaveType"), _
                                           Reader.Item("AbsenceDetails"), Reader.Item("DecisionMade"), _
                                           Reader.Item("SavedBy"), CDate(Reader.Item("TransDate")).ToString("dd-MMM-yyyy")})
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Function CheckYesNo(ByVal Value As Integer) As String
        If Value = 0 Then
            Return "No"
        Else
            Return "Yes"
        End If
    End Function

    Sub Clear()
        Me.txtName.Clear()
        Me.txtPosition.Clear()
        Me.txtBranch.Clear()
        Me.DTPFrom.Value = Now
        Me.DTPTo.Value = Now
        Me.txtNoOfDays.Value = 1
        Me.ChDeductedfromVacation.Checked = False
        Me.ListLeaveType.SelectedIndex = -1
        Me.txtAbsenceDetails.Clear()
        Me.txtDecisionMade.Clear()
        Me.GridAbsenceHistory.Rows.Clear()
    End Sub

    Private Sub butClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butClear.Click
        Me.DTPFrom.Value = Now
        Me.DTPTo.Value = Now
        Me.txtNoOfDays.Value = 1
        Me.ChDeductedfromVacation.Checked = True
        Me.ListLeaveType.SelectedIndex = -1
        Me.txtAbsenceDetails.Clear()
        Me.txtDecisionMade.Clear()
    End Sub

    Private Sub butClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butClose.Click
        Me.Close()
    End Sub

    Private Sub butSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butSave.Click
        Me.ErrProv.Clear()
        If Me.txtName.Text.Trim.Length = 0 Then
            MsgBox("Please enter a valid staff ID!")

        ElseIf Me.DTPFrom.Value > Me.DTPTo.Value Then
            Me.ErrProv.SetError(Me.DTPFrom, "Please fill in all details")
            Me.DTPFrom.Focus()

        ElseIf Me.txtNoOfDays.Value = 0 Then
            Me.ErrProv.SetError(Me.txtNoOfDays, "Please fill in all details")
            Me.txtNoOfDays.Focus()

        ElseIf Me.ListLeaveType.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.ListLeaveType, "Please fill in all details")

        ElseIf Me.txtAbsenceDetails.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtAbsenceDetails, "Please fill in all details")
            Me.txtAbsenceDetails.Focus()

        ElseIf Me.txtDecisionMade.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtDecisionMade, "Please fill in all details")
            Me.txtDecisionMade.Focus()

        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand
                Dim Trans As SqlTransaction

                cnn.Open()
                Trans = cnn.BeginTransaction
                cmd.Transaction = Trans
                cmd.Connection = cnn

                'Save Absence details
                cmd.CommandText = "Insert Into Absence " & _
                                  "(EmpNo,Name,AbsentDateFrom,AbsentDateTo,AbsenceNoOfDays,DeductedfromVacation,LeaveType,AbsenceDetails,DecisionMade,SavedBy) " & _
                                  "Values " & _
                                  "(@EmpNo,@Name,@AbsentDateFrom,@AbsentDateTo,@AbsenceNoOfDays,@DeductedfromVacation,@LeaveType,@AbsenceDetails,@DecisionMade,@SavedBy)"

                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@EmpNo", Me.txtEmpNo.Text)
                cmd.Parameters.AddWithValue("@Name", Me.txtName.Text)
                cmd.Parameters.AddWithValue("@AbsentDateFrom", Me.DTPFrom.Value)
                cmd.Parameters.AddWithValue("@AbsentDateTo", Me.DTPTo.Value)
                cmd.Parameters.AddWithValue("@AbsenceNoOfDays", Me.txtNoOfDays.Value)
                cmd.Parameters.AddWithValue("@DeductedfromVacation", CInt(Me.ChDeductedfromVacation.CheckState))
                cmd.Parameters.AddWithValue("@LeaveType", Me.ListLeaveType.SelectedItem)
                cmd.Parameters.AddWithValue("@AbsenceDetails", Me.txtAbsenceDetails.Text.Trim)
                cmd.Parameters.AddWithValue("@DecisionMade", Me.txtDecisionMade.Text.Trim)
                cmd.Parameters.AddWithValue("@SavedBy", CurrentUser)
                cmd.ExecuteNonQuery()

                'Chech if to deducte from vacation
                If Me.ChDeductedfromVacation.Checked = True Then
                    cmd.CommandText = "Insert Into StaffLeave " & _
                                     "(EmpID,EmpName,PeriodFrom,PeriodTo,NoOfDays,LeaveReson,ContactDetails,SavedBy) " & _
                                     "Values " & _
                                     "(@EmpID,@EmpName,@PeriodFrom,@PeriodTo,@NoOfDays,@LeaveReson,@ContactDetails,@SavedBy)"
                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@EmpID", Me.txtEmpNo.Text)
                    cmd.Parameters.AddWithValue("@EmpName", Me.txtName.Text)
                    cmd.Parameters.AddWithValue("@PeriodFrom", Me.DTPFrom.Value)
                    cmd.Parameters.AddWithValue("@PeriodTo", Me.DTPTo.Value)
                    cmd.Parameters.AddWithValue("@NoOfDays", Me.txtNoOfDays.Value)
                    cmd.Parameters.AddWithValue("@LeaveReson", Me.txtAbsenceDetails.Text.Trim)
                    cmd.Parameters.AddWithValue("@ContactDetails", "-")
                    cmd.Parameters.AddWithValue("@SavedBy", CurrentUser)
                    cmd.ExecuteNonQuery()
                End If

                Trans.Commit()
                cnn.Close()

                MsgBox("Saved successfully!")

                Me.DTPFrom.Value = Now
                Me.DTPTo.Value = Now
                Me.txtNoOfDays.Value = 1
                Me.ChDeductedfromVacation.Checked = False
                Me.ListLeaveType.SelectedIndex = -1
                Me.txtAbsenceDetails.Clear()
                Me.txtDecisionMade.Clear()

                FillAbsenceHistory()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Sub FillEmpData()
        If Me.ListUsers.SelectedItems.Count <= 0 Then
            Exit Sub
        Else

            Try
                Me.Cursor = Cursors.WaitCursor
                Dim cmd As New SqlCommand("Select EmpNo,FullNameEn,Position, Branch From StaffProfiles where EmpNo= @EmpNo", cnn)
                cmd.Parameters.AddWithValue("@EmpNo", Me.ListUsers.SelectedItems(0).Text)
                Dim Reader As SqlDataReader
                Me.txtName.Clear()
                Me.txtPosition.Clear()
                Me.txtBranch.Clear()
                cnn.Open()
                Reader = cmd.ExecuteReader
                While Reader.Read
                    Me.txtEmpNo.Text = Reader.Item("EmpNo")
                    Me.txtName.Text = Reader.Item("FullNameEn")
                    Me.txtPosition.Text = Reader.Item("Position")
                    Me.txtBranch.Text = Reader.Item("Branch")
                End While
                cnn.Close()
                FillAbsenceHistory()
                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub txtEmpNo_KeyUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtEmpNo.KeyUp
        If e.KeyCode = Keys.Enter Then
            FillEmpData()
        End If
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim a As New frmSearchEmp
        a.ShowDialog()
        If SelEmpID = 0 Then
            Exit Sub
        End If
        Me.txtEmpNo.Text = SelEmpID
        FillEmpData()
    End Sub


    Private Sub txtEmpNo_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtEmpNo.TextChanged
        Clear()
    End Sub

    Private Sub frmStaffAbsentRegiter_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        FillStaff()
        Me.WindowState = FormWindowState.Maximized
    End Sub




    Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrint.Click
        If txtName.Text.Trim.Length = 0 Then
            MsgBox("Please enter a valid staff ID!")
            Exit Sub
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                ' Set Report DataSet 
                Dim das As New DataSet
                Dim dap As New SqlDataAdapter("Select * From Absents Where (EmpNo=@EmpNo)", cnn)
                dap.SelectCommand.Parameters.AddWithValue("@EmpNo", Me.txtEmpNo.Text)


                dap.Fill(das, "Absents")

                ' Creat Report and Pass The data
                Dim rpt As New StaffAbsents
                rpt.SetDataSource(das)
                rptViewer.CrystalReportViewer1.ReportSource = rpt
                rptViewer.CrystalReportViewer1.RefreshReport()
                rptViewer.ShowDialog()

                txtEmpNo.Focus()
                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub ListUsers_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListUsers.SelectedIndexChanged
        Clear()

        If Me.ListUsers.SelectedItems.Count > 0 Then
            Try
                Me.Cursor = Cursors.WaitCursor
                Dim cmd As New SqlCommand("Select EmpNo,FullNameEn,Position,Branch,VacationPeriod " & _
                                          "From StaffProfiles where EmpNo=" & CInt(Me.ListUsers.SelectedItems(0).Text), cnn)
                Dim Reader As SqlDataReader

                cnn.Open()
                Reader = cmd.ExecuteReader
                While Reader.Read
                    Me.txtEmpNo.Text = Reader.Item("EmpNo")
                    Me.txtName.Text = Reader.Item("FullNameEn")
                    Me.txtPosition.Text = Reader.Item("Position")
                    Me.txtBranch.Text = Reader.Item("Branch")
                End While
                Reader.Close()
                cnn.Close()

                FillAbsenceHistory()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Sub Calculate()
        Try
            Me.txtNoOfDays.Value = CInt(DateDiff(DateInterval.DayOfYear, Me.DTPFrom.Value, Me.DTPTo.Value)) + 1
        Catch ex As Exception

        End Try
    End Sub

    Private Sub DTPFrom_ValueChanged(sender As System.Object, e As System.EventArgs) Handles DTPFrom.ValueChanged
        Calculate()
    End Sub

    Private Sub DTPTo_ValueChanged(sender As System.Object, e As System.EventArgs) Handles DTPTo.ValueChanged
        Calculate()
    End Sub
End Class